package pos.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import empleados.Incidencia;

public class JDBCIncidenciaDAO implements IIncidenciaDAO{
	
private Connection conn;
	
	public JDBCIncidenciaDAO(){
		
		conn = ConnectionManager.getInstance().checkOut();
		
		
	}

	@Override
	public void delete(int id) {
		String sql = "DELETE FROM incidencias WHERE (id = ?) ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, id);
            stmt.executeUpdate();
            
        } catch (SQLException e) {
            System.err.println("Message: " + e.getMessage());
            System.err.println("SQLState: " + e.getSQLState());
            System.err.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }
        }
	}
		
	

	@Override
	public void insert(Incidencia i) {
		String sql = "INSERT INTO incidencias (dniemp, desc) VALUES (?, ?) ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, i.getDNIemp());
            stmt.setString(2, i.getDescripcion());               
            stmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		
	}

	@Override
	public void insertincsancion(int idincidencia, int idsancion) {
		String sql = "INSERT INTO incisancion (idincidencia, idsancion) VALUES (?, ?) ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);

            stmt.setInt(1, idincidencia);
            stmt.setInt(2, idsancion);
            
            stmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		
	}

	@Override
	public void modificaSancion(int idsancion, String sancion) {
		PreparedStatement stmt = null;
		ResultSet result = null;
		String sql = "UPDATE sanciones SET desc = ? WHERE (id = ?)";
		
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, sancion);
			stmt.setInt(2, idsancion);		
			stmt.executeUpdate();
		}catch(SQLException e){
			System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		
	}

	@Override
	public Incidencia select(int idincidencia) {
		PreparedStatement stmt = null;
        ResultSet result = null;
        
        Incidencia i = null;
       
        String sql = "SELECT * FROM incidencias WHERE (id = ?) ";
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, idincidencia);
            result = stmt.executeQuery();

            if(result.next()){
            	i = new Incidencia();            	
            	int id = Integer.parseInt(result.getString("id"));
            	i.setIdIncidencia(id);
            	i.setDNIemp(result.getString("dniemp"));
            	i.setDescripcion(result.getString("descripcion"));            	         	
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
        return i;
	}

	@Override
	public int selectIdIncidencia(String dni, String desc) {
		PreparedStatement stmt = null;
        ResultSet result = null;       
        int id=0;       
        String sql = "SELECT * FROM incidencias WHERE (dniemp = ? AND desc = ?)";
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, dni);
            stmt.setString(2, desc);      
            result = stmt.executeQuery();

            if(result.next()){
            	id = result.getInt("id");
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
        return id;
	}

	@Override
	public List<Incidencia> selectiempleado(String dni) {
		PreparedStatement stmt = null;
        ResultSet result = null;        
        List<Incidencia> listancidencias = new LinkedList<Incidencia>();        
        String sql = "SELECT * FROM incidencias WHERE(dniemp = ?) ";
    
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, dni);
            result = stmt.executeQuery();

            while (result.next()) {
                Incidencia i = new Incidencia();
                int id = Integer.parseInt(result.getString("idincidencia"));
                i.setIdIncidencia(id);           	
            	i.setDNIemp(dni);
            	i.setDescripcion(result.getString("desc"));                             
            	listancidencias.add(i);
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
        return listancidencias;
	}


	@Override
	public Incidencia selectincsancion(Incidencia i) {
		PreparedStatement stmt = null;
        ResultSet result = null;
        
       
        String sql = "SELECT * FROM incisancion WHERE (idincidencia = ?) ";
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, i.getIdIncidencia());
            result = stmt.executeQuery();

            if(result.next()){            	
            	int id = Integer.parseInt(result.getString("idsancion"));
            	i.setIdSancion(id);            	
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
        return i;
	}
	
	public void finalize(){
		ConnectionManager.getInstance().checkIn(conn);
		
	}

}
